<?php

namespace App\Service\delivery;
use App\Model\Applet;
use App\Model\AppletPlayletRel;
use App\Model\DataSpread;
use App\Model\Payment;
use App\Model\Playlet;
use App\Model\Company;
use App\Model\AppletCompany;
use App\Model\PlayletEpisodes;
use App\Model\RechargeGoods;
use App\Model\RechargeTemplate;
use App\Model\SystemOrderDay;
use App\Model\SystemUserDay;
use App\Service\CommicService;
use Hyperf\Utils\ApplicationContext;
use App\Common\Response\SystemCode;
use Hyperf\DbConnection\Db;

class RealStatisticsService extends CommicService
{
    const cacheDuration = 60 * 15;
    public function __construct()
    {
        $container = ApplicationContext::getContainer();
        $this->redisService = $container->get(\Hyperf\Redis\Redis::class);
        $this->userAppletIds = $this->getUserAppletInfo();
    }
    const STATUS_ENABLE  = 1;

    public function spreadRealSearch(Array $params,$export=0){

        $pageSize       = $params['pageSize'] ?? 10;
        $page           = $params['page'] ?? 1;
        $applet_ids     = $params['applet_ids'] ?? '';
        $data_spread_id = $params['data_spread_id'] ?? '';
        $spread_name    = $params['keyword'] ?? '';
        $playlet_id     = $params['playlet_id'] ?? '';
        $delivery_user_id  = $params['delivery_user_id'] ?? '';
        $subSelect = '';
        if(empty($applet_ids)){
            return [];
        }

        //2023-12-11 新增可看到投放组员的数据
        if(!empty($delivery_user_id)){
//            $spreadIds = DataSpread::query()->whereIn('user_id',$delivery_user_id)->pluck('id');
            $delivery_str = implode(',',$delivery_user_id);
            $subSelect = "select `id` from `data_spread` where `user_id` in (".$delivery_str.") and `data_spread`.`deleted_at` is null";
        }else{
            $userInfo = $this->getUserAppletInfo(2);
            //管理员能看到所有
            if($userInfo['role']['is_admin'] != 1){
//                $spreadIds = DataSpread::query()->where(['user_id' => $userInfo['id']])->pluck('id');
                $subSelect = "select `id` from `data_spread` where `user_id` = ".$userInfo['id']." and `data_spread`.`deleted_at` is null";
            }
        }

        if($applet_ids == '1'){
            //获取所有的小程序所绑定的短剧
            $applet_id =  $this->userAppletIds;
        }else{
            $applet_id =  $applet_ids;
        }
        $selectArr = ['data_payment.spread_id as data_spread_id','data_spread.name as spread_name'];
        $groupBy = ['data_payment.spread_id','data_spread.name'];
        if($playlet_id != '' && $playlet_id != 1){
            $selectArr[] ='data_payment.playlet_id';
            $selectArr[] ='playlet.name as playlet_name';
            $groupBy[] = 'data_payment.playlet_id';
            $groupBy[] = 'playlet.name';
        }

        if($applet_id != '' && $applet_ids != 1){
            $selectArr[] ='data_payment.applet_id';
            $groupBy[] = 'applet_id';
        }

        $commonArr = [
            Db::raw("count(data_payment.id) as order_pay_count"),
            Db::raw("count(data_payment.id) as order_count"),
            Db::raw("count(IF(data_payment.`first_recharge`='Y',data_payment.`id`,null)) as first_user_count"),
            Db::raw("SUM(IF(data_payment.`first_recharge`='Y',data_payment.`amount`,0)) as first_user_pay_amonut"),
            Db::raw("count(IF(data_payment.`today_first_recharge`='Y',data_payment.`id`,null)) as today_first_user_pay_count"),
            Db::raw("SUM(IF(data_payment.`today_first_recharge`='Y',data_payment.`amount`,0)) as today_first_user_pay_amonut"),
            Db::raw("SUM(IF(data_payment.`today_after_recharge`='Y',data_payment.`amount`,0)) as today_after_user_pay_amonut"),
            Db::raw("count(IF(data_payment.`pay_type`=1,data_payment.`id`,null)) as order_virtually_pay_count"),
            Db::raw("count(IF(data_payment.`pay_type`=2,data_payment.`id`,null)) as order_wx_pay_count"),
            Db::raw("SUM(IF(data_payment.`pay_type`=1,data_payment.`amount`,0)) as order_virtually_pay_amount"),
            Db::raw("SUM(IF(data_payment.`pay_type`=2,data_payment.`amount`,0)) as order_wx_pay_amonut"),
            Db::raw("SUM(data_payment.`amount`) as order_amount"),
        ];

        $selectArr = array_merge($selectArr,$commonArr);

        //获取所拥有的

        $query = Payment::query()->select($selectArr);
        $countQuery = Payment::query()->select($commonArr);
        if($data_spread_id != ''){
            $query->where(['data_payment.spread_id' => $data_spread_id]);
            $countQuery->where(['data_payment.spread_id' => $data_spread_id]);
        }
        if(!empty($spread_name)){
            $selectIds =  DataSpread::query()->Where([['name', 'like', '%'.$spread_name.'%']])->pluck('id');
            if(!empty($selectIds)){
                $query->whereIn('data_payment.spread_id',$selectIds);
                $countQuery->whereIn('data_payment.spread_id',$selectIds);
            }
        }
        if($playlet_id != '' && $playlet_id != 1){
            $query->where('data_payment.playlet_id',$playlet_id);
            $countQuery->where('data_payment.playlet_id',$playlet_id);
        }
        if($applet_id != '' && $applet_ids != 1){
            $query->where('data_payment.applet_id',$applet_ids);
            $countQuery->where('data_payment.applet_id',$applet_ids);
        }
        if(!empty($subSelect)){
            $query->whereRaw('data_payment.spread_id in ('.$subSelect.')');
            $countQuery->whereRaw('data_payment.spread_id in ('.$subSelect.')');
        }
        if(!empty($spreadIds)){
            $query->whereIn('data_payment.spread_id',$spreadIds);
            $countQuery->whereIn('data_payment.spread_id',$spreadIds);
        }
        $query->groupBy($groupBy);
        $query->where('data_payment.spread_id','!=',0);
        $countQuery->where('data_payment.spread_id','!=',0);

        $models = $query
            ->leftJoin('data_spread','data_payment.spread_id','=','data_spread.id')
            ->leftJoin('playlet','data_payment.playlet_id','=','playlet.id')
            ->where('pay_time','>=',date('Y-m-d 00:00:00',time()))->where('pay_time','<=',date('Y-m-d 23:59:59',time()))
            ->paginate($pageSize,['*'],'page',$page);


        $countResult = $countQuery->where('pay_time','>=',date('Y-m-d 00:00:00',time()))->where('pay_time','<=',date('Y-m-d 23:59:59',time()))->get()->toArray();

        $countInsertData = array();
        foreach ($countResult as $key => $item){
            $countInsertData = $item;
            $countInsertData['date'] = '汇总';
            $countInsertData['data_spread_id'] = '汇总';
            $countInsertData['spread_name'] = '汇总';
            $countInsertData['playlet_name'] = '汇总';
            $countInsertData['first_user_pay_amonut']  = sprintf("%.2f",floatval($item['first_user_pay_amonut']/100));
            $countInsertData['today_first_user_pay_amonut']  = sprintf("%.2f",floatval($item['today_first_user_pay_amonut']/100));
            $countInsertData['today_after_user_pay_amonut']  = sprintf("%.2f",floatval($item['today_after_user_pay_amonut']/100));
            $countInsertData['today_all_user_pay_amonut']  = sprintf("%.2f",floatval($countInsertData['today_after_user_pay_amonut']+$countInsertData['today_first_user_pay_amonut']));
            if(!empty($item['today_first_user_pay_count']) && !empty($countInsertData['today_all_user_pay_amonut'])){
                $countInsertData['today_user_arpu'] = sprintf("%.2f",($countInsertData['today_all_user_pay_amonut']/$item['today_first_user_pay_count']));
            }else{
                $countInsertData['today_user_arpu'] = 0;
            }
            $countInsertData['order_wx_pay_amonut']  = sprintf("%.2f",floatval($item['order_wx_pay_amonut']/100));
            $countInsertData['order_virtually_pay_amount']  =sprintf("%.2f",floatval($item['order_virtually_pay_amount']/100));
            $countInsertData['order_amount']  = sprintf("%.2f",floatval($item['order_amount']/100));
        }
        $countData[0] = $countInsertData;
        $items = $models->items();
        $selectData = [];
        foreach ($items as $item){
            $data = $item->toArray();
            $data['date'] = '当天实时';
            $data['today_first_user_pay_amonut']  = sprintf("%.2f",floatval($data['today_first_user_pay_amonut']/100));
            $data['today_after_user_pay_amonut']  = sprintf("%.2f",floatval($data['today_after_user_pay_amonut']/100));
            $data['today_all_user_pay_amonut']  = sprintf("%.2f",floatval($data['today_after_user_pay_amonut']+$data['today_first_user_pay_amonut']));

            if(!empty($data['today_first_user_pay_count']) && !empty($data['today_all_user_pay_amonut'])){
                $data['today_user_arpu'] = sprintf("%.2f",($data['today_all_user_pay_amonut']/$data['today_first_user_pay_count']));
            }else{
                $data['today_user_arpu'] = 0;
            }

            $data['first_user_pay_amonut']  = sprintf("%.2f",floatval($data['first_user_pay_amonut']/100));
            $data['order_wx_pay_amonut']  = sprintf("%.2f",floatval($data['order_wx_pay_amonut']/100));
            $data['order_virtually_pay_amount']  =sprintf("%.2f",floatval($data['order_virtually_pay_amount']/100));
            $data['order_amount']  = sprintf("%.2f",floatval($data['order_amount']/100));
            $selectData[] =$data;
        }
        $data = $models->toArray();
//        $data['data'] = $selectData;
        $data['data'] = array_merge($countData,$selectData);
        return $data;
    }
    public function orderRealSearch(Array $params,$export=0){

        $pageSize       = $params['pageSize'] ?? 10;
        $page           = $params['page'] ?? 1;
        $applet_ids     = $params['applet_ids'] ?? '';
        $playlet_id     = $params['playlet_id'] ?? '';
        $delivery_user_id  = $params['delivery_user_id'] ?? '';
        if(empty($applet_ids)){
            return [];
        }
        $subSelect = '';
        //2023-12-11 新增可看到投放组员的数据
        if(!empty($delivery_user_id)){

//            $spreadIds = DataSpread::query()->whereIn('user_id',$delivery_user_id)->pluck('id');
            $delivery_str = implode(',',$delivery_user_id);
            $subSelect = "select `id` from `data_spread` where `user_id` in (".$delivery_str.") and `data_spread`.`deleted_at` is null";

        }else{
            $userInfo = $this->getUserAppletInfo(2);
            //管理员能看到所有
            if(empty($userInfo['role']['is_admin']) && empty($userInfo['role']['is_statistical'])){
//                $spreadIds = DataSpread::query()->where(['user_id' => $userInfo['id']])->pluck('id');
                $subSelect = "select `id` from `data_spread` where `user_id` = ".$userInfo['id']." and `data_spread`.`deleted_at` is null";
            }
        }




        $nowArr = [
            Db::raw("count('id') as order_pay_count"),
            Db::raw("count(IF(data_payment.`first_recharge`='Y',data_payment.`id`,null)) as first_user_count"),
            Db::raw("SUM(IF(data_payment.`first_recharge`='Y',data_payment.`amount`,0)) as first_user_pay_amonut"),
            Db::raw("count(IF(data_payment.`today_first_recharge`='Y',data_payment.`id`,null)) as today_first_user_pay_count"),
            Db::raw("SUM(IF(data_payment.`today_first_recharge`='Y',data_payment.`amount`,0)) as today_first_user_pay_amonut"),
            Db::raw("SUM(IF(data_payment.`today_after_recharge`='Y',data_payment.`amount`,0)) as today_after_user_pay_amonut"),
            Db::raw("count(IF(`pay_type`=1,`id`,null)) as order_virtually_pay_count"),
            Db::raw("count(IF(`pay_type`=2,`id`,null)) as order_wx_pay_count"),
            Db::raw("SUM(IF(`pay_type`=1,`amount`,0)) as order_virtually_pay_amount"),
            Db::raw("SUM(IF(`pay_type`=2,`amount`,0)) as order_wx_pay_amonut"),
            Db::raw("SUM(`amount`) as order_amount"),
        ];
        $countnewData = array();
        $query = Payment::query();
        if(!empty($spreadIds)){
            $query->whereIn('data_payment.spread_id',$spreadIds);
        }
        if(!empty($subSelect)){
            $query->whereRaw('data_payment.spread_id in ('.$subSelect.')');
        }

        if($applet_ids != 1){
            $query->where('data_payment.applet_id',$applet_ids);
        }
        if($playlet_id != '' && $playlet_id != 1){
            $query->where('playlet_id',$playlet_id);
        }
        //实时今天的订单有多少
        $nowData = $query->where('pay_time','>=',date('Y-m-d 00:00:00',time()))->where('pay_time','<=',date('Y-m-d 23:59:59',time()))->select($nowArr)->get();
        foreach ($nowData as $key => $item){
            $countnewData = $item;
            $countnewData['date'] = '当天实时';
            $countnewData['playlet_name'] = '当天实时';
            $countnewData['first_user_pay_amonut']  = sprintf("%.2f",floatval($item['first_user_pay_amonut']/100));
            $countnewData['today_first_user_pay_amonut']  = sprintf("%.2f",floatval($item['today_first_user_pay_amonut']/100));
            $countnewData['today_after_user_pay_amonut']  = sprintf("%.2f",floatval($item['today_after_user_pay_amonut']/100));
            $countnewData['order_wx_pay_amonut']  = sprintf("%.2f",floatval($item['order_wx_pay_amonut']/100));
            $countnewData['order_virtually_pay_amount']  =sprintf("%.2f",floatval($item['order_virtually_pay_amount']/100));
            $countnewData['order_amount']  = sprintf("%.2f",floatval($item['order_amount']/100));
            $countnewData['today_all_user_pay_amonut'] = sprintf("%.2f",floatval($countnewData['today_first_user_pay_amonut']+$countnewData['today_after_user_pay_amonut']));
            if(!empty($item['today_first_user_pay_count']) && !empty($countnewData['today_all_user_pay_amonut'])){
                $countnewData['today_user_arpu'] = sprintf("%.2f",($countnewData['today_all_user_pay_amonut']/$item['today_first_user_pay_count']));
            }else{
                $countnewData['today_user_arpu'] = 0;
            }
        }
        $countData[0] = $countnewData;
        $selectData = [];
        $data['data'] = $countData;
        return $data;
    }

}